package com.example.demo;

import com.sun.net.httpserver.HttpExchange;
import com.sun.net.httpserver.HttpHandler;
import com.sun.net.httpserver.HttpServer;
import com.example.demo.DatabaseHelper;

// 添加Apache POI导入
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.*;
import java.net.InetSocketAddress;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.Executors;
import java.util.stream.Collectors;

public class SimpleHttpServer {
    private static final int PORT = 8091;
    private static final String STATIC_DIR = "src/main/resources/static";
    
    // 主方法
    public static void main(String[] args) {
        try {
            // 创建HTTP服务器，监听8091端口
            HttpServer server = HttpServer.create(new InetSocketAddress(PORT), 0);
            
            // 设置线程池
            server.setExecutor(Executors.newFixedThreadPool(10));
            
            // 添加请求处理器
            server.createContext("/", new StaticFileHandler());
            server.createContext("/api/data", new DataApiHandler());
            server.createContext("/api/upload-excel", new ExcelUploadHandler());
            
            // 启动服务器
            server.start();
            
            System.out.println("服务器已启动，监听端口: " + PORT);
            System.out.println("可通过以下地址访问:");
            System.out.println("- http://localhost:" + PORT + "/ (将自动重定向到页面1)");
            System.out.println("- http://localhost:" + PORT + "/page1.html");
            System.out.println("- http://localhost:" + PORT + "/page2.html");
            System.out.println("- http://localhost:" + PORT + "/page3.html");
            System.out.println("- http://localhost:" + PORT + "/page4.html");
            
        } catch (IOException e) {
            System.err.println("启动服务器失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    // 静态文件处理器
    static class StaticFileHandler implements HttpHandler {
        @Override
        public void handle(HttpExchange exchange) throws IOException {
            String path = exchange.getRequestURI().getPath();
            
            // 如果请求的是根路径，重定向到page1.html
            if ("/".equals(path)) {
                exchange.getResponseHeaders().add("Location", "/page1.html");
                exchange.sendResponseHeaders(302, -1);
                return;
            }
            
            // 获取静态资源路径
            Path filePath = Paths.get(STATIC_DIR + path);
            
            if (Files.exists(filePath) && !Files.isDirectory(filePath)) {
                // 读取文件内容
                byte[] fileContent = Files.readAllBytes(filePath);
                
                // 设置Content-Type
                String contentType = getContentType(path);
                exchange.getResponseHeaders().add("Content-Type", contentType);
                
                // 返回响应
                exchange.sendResponseHeaders(200, fileContent.length);
                try (OutputStream os = exchange.getResponseBody()) {
                    os.write(fileContent);
                }
            } else {
                // 文件不存在，返回404
                String response = "404 - 找不到文件: " + path;
                exchange.sendResponseHeaders(404, response.getBytes(StandardCharsets.UTF_8).length);
                try (OutputStream os = exchange.getResponseBody()) {
                    os.write(response.getBytes(StandardCharsets.UTF_8));
                }
            }
        }
        
        // 根据文件扩展名获取Content-Type
        private String getContentType(String path) {
            if (path.endsWith(".html")) {
                return "text/html; charset=UTF-8";
            } else if (path.endsWith(".css")) {
                return "text/css; charset=UTF-8";
            } else if (path.endsWith(".js")) {
                return "application/javascript; charset=UTF-8";
            } else if (path.endsWith(".json")) {
                return "application/json; charset=UTF-8";
            } else if (path.endsWith(".png")) {
                return "image/png";
            } else if (path.endsWith(".jpg") || path.endsWith(".jpeg")) {
                return "image/jpeg";
            } else if (path.endsWith(".gif")) {
                return "image/gif";
            } else if (path.endsWith(".svg")) {
                return "image/svg+xml";
            } else {
                return "text/plain; charset=UTF-8";
            }
        }
    }
    
    // 数据API处理器
    static class DataApiHandler implements HttpHandler {
        @Override
        public void handle(HttpExchange exchange) throws IOException {
            String method = exchange.getRequestMethod();
            String path = exchange.getRequestURI().getPath();
            String query = exchange.getRequestURI().getQuery();
            
            Map<String, String> queryParams = parseQueryParams(query);
            
            try {
                // 确定操作的页面
                String page = queryParams.getOrDefault("page", "page1");
                
                // 根据HTTP方法执行不同操作
                switch (method) {
                    case "GET":
                        handleGetRequest(exchange, page, queryParams);
                        break;
                    case "POST":
                        handlePostRequest(exchange, page);
                        break;
                    case "PUT":
                        handlePutRequest(exchange, page, queryParams);
                        break;
                    case "DELETE":
                        handleDeleteRequest(exchange, page, queryParams);
                        break;
                    default:
                        sendErrorResponse(exchange, 405, "不支持的HTTP方法: " + method);
                }
            } catch (Exception e) {
                e.printStackTrace();
                sendErrorResponse(exchange, 500, "服务器错误: " + e.getMessage());
            }
        }
        
        // 处理GET请求，查询数据
        private void handleGetRequest(HttpExchange exchange, String page, Map<String, String> queryParams) throws IOException {
            try {
                // 解析分页参数（兼容新旧参数名）
                int pageNum = Integer.parseInt(queryParams.getOrDefault("pageNum", queryParams.getOrDefault("page_num", "1")));
                int pageSize = Integer.parseInt(queryParams.getOrDefault("pageSize", queryParams.getOrDefault("page_size", "10")));
                
                // 检查是否是按ID查询（只有当没有分页参数时才认为是ID查询）
                String idParam = queryParams.get("id");
                if (idParam != null && !idParam.trim().isEmpty() && 
                    !queryParams.containsKey("pageNum") && !queryParams.containsKey("page_num")) {
                    // 按ID查询单条记录
                    try {
                        int id = Integer.parseInt(idParam);
                        List<Map<String, Object>> data = DatabaseHelper.getDataById(page, id);
                        
                        Map<String, Object> pagination = new HashMap<>();
                        pagination.put("total", data.size());
                        pagination.put("totalPages", data.size() > 0 ? 1 : 0);
                        pagination.put("currentPage", 1);
                        pagination.put("pageSize", 10);

                        Map<String, Object> response = new HashMap<>();
                        response.put("success", true);
                        response.put("message", "查询成功");
                        response.put("data", data);
                        response.put("pagination", pagination);

                        sendJsonResponse(exchange, response);
                        return;
                    } catch (NumberFormatException e) {
                        sendErrorResponse(exchange, 400, "无效的ID参数");
                        return;
                    }
                }
                
                // 解析搜索参数
                Map<String, String> searchParams = new HashMap<>();
                for (Map.Entry<String, String> entry : queryParams.entrySet()) {
                    String key = entry.getKey();
                    String value = entry.getValue();
                    if (!key.equals("page") && !key.equals("pageNum") && !key.equals("pageSize") && 
                        !key.equals("page_num") && !key.equals("page_size") && !key.equals("id") && 
                        value != null && !value.trim().isEmpty()) {
                        searchParams.put(key, value.trim());
                    }
                }
                
                // 构建搜索条件字符串（为了兼容现有的DatabaseHelper方法）
                String search = "";
                if (!searchParams.isEmpty()) {
                    StringBuilder sb = new StringBuilder();
                    for (Map.Entry<String, String> entry : searchParams.entrySet()) {
                        if (sb.length() > 0) sb.append(" AND ");
                        sb.append(entry.getKey()).append("=").append(entry.getValue());
                    }
                    search = sb.toString();
                }
                
                // 获取总记录数
                int total = DatabaseHelper.getDataCount(page, search, null);
                
                // 获取分页数据  
                List<Map<String, Object>> data = DatabaseHelper.queryData(page, search, pageNum, pageSize, null);
                
                // 计算总页数
                int totalPages = (int) Math.ceil((double) total / pageSize);
                
                // 构建符合前端期望的响应格式
                Map<String, Object> pagination = new HashMap<>();
                pagination.put("total", total);
                pagination.put("totalPages", totalPages);
                pagination.put("currentPage", pageNum);
                pagination.put("pageSize", pageSize);
                
                Map<String, Object> response = new HashMap<>();
                response.put("success", true);
                response.put("message", "查询成功");
                response.put("data", data);
                response.put("pagination", pagination);
                
                sendJsonResponse(exchange, response);
            } catch (Exception e) {
                e.printStackTrace();
                sendErrorResponse(exchange, 500, "查询失败: " + e.getMessage());
            }
        }
        
        // 处理POST请求，添加数据
        private void handlePostRequest(HttpExchange exchange, String page) throws IOException {
            try {
                // 读取请求体
                String requestBody = readRequestBody(exchange);
                Map<String, Object> data = parseJsonToMap(requestBody);
                
                // 调试日志：打印接收到的数据
                
                
                // 添加数据
                int newId = DatabaseHelper.addData(page, data, null);
                
                if (newId > 0) {
                    Map<String, Object> response = new HashMap<>();
                    response.put("success", true);
                    response.put("message", "添加成功");
                    response.put("data", newId);
                    
                    sendJsonResponse(exchange, response);
                } else {
                    Map<String, Object> response = new HashMap<>();
                    response.put("success", false);
                    response.put("message", "添加失败");
                    sendJsonResponse(exchange, response);
                }
            } catch (Exception e) {
                e.printStackTrace();
                sendErrorResponse(exchange, 500, "添加失败: " + e.getMessage());
            }
        }
        
        // 处理PUT请求，更新数据
        private void handlePutRequest(HttpExchange exchange, String page, Map<String, String> queryParams) throws IOException {
            try {
                // 获取ID
                int id = Integer.parseInt(queryParams.getOrDefault("id", "0"));
                if (id <= 0) {
                    sendErrorResponse(exchange, 400, "缺少有效的ID参数");
                    return;
                }
                
                // 读取请求体
                String requestBody = readRequestBody(exchange);
                
                // 调试日志：打印原始JSON
                            Map<String, Object> data = parseJsonToMap(requestBody);
                
                // 更新数据
                boolean success = DatabaseHelper.updateData(page, id, data);
                
                if (success) {
                    Map<String, Object> response = new HashMap<>();
                    response.put("success", true);
                    response.put("message", "更新成功");
                    
                    sendJsonResponse(exchange, response);
                } else {
                    Map<String, Object> response = new HashMap<>();
                    response.put("success", false);
                    response.put("message", "更新失败");
                    sendJsonResponse(exchange, response);
                }
            } catch (Exception e) {
                e.printStackTrace();
                sendErrorResponse(exchange, 500, "更新失败: " + e.getMessage());
            }
        }
        
        // 处理DELETE请求，删除数据
        private void handleDeleteRequest(HttpExchange exchange, String page, Map<String, String> queryParams) throws IOException {
            try {
                // 获取ID
                int id = Integer.parseInt(queryParams.getOrDefault("id", "0"));
                if (id <= 0) {
                    sendErrorResponse(exchange, 400, "缺少有效的ID参数");
                    return;
                }
                
                // 删除数据
                boolean success = DatabaseHelper.deleteData(page, id);
                
                if (success) {
                    Map<String, Object> response = new HashMap<>();
                    response.put("success", true);
                    response.put("message", "删除成功");
                    
                    sendJsonResponse(exchange, response);
                } else {
                    Map<String, Object> response = new HashMap<>();
                    response.put("success", false);
                    response.put("message", "删除失败");
                    sendJsonResponse(exchange, response);
                }
            } catch (Exception e) {
                e.printStackTrace();
                sendErrorResponse(exchange, 500, "删除失败: " + e.getMessage());
            }
        }
    }
    
    // Excel文件上传处理器
    static class ExcelUploadHandler implements HttpHandler {
        @Override
        public void handle(HttpExchange exchange) throws IOException {
            if (!"POST".equals(exchange.getRequestMethod())) {
                sendErrorResponse(exchange, 405, "不支持的HTTP方法: " + exchange.getRequestMethod());
                return;
            }
            
            String query = exchange.getRequestURI().getQuery();
            Map<String, String> queryParams = parseQueryParams(query);
            
            try {
                // 确定操作的页面
                String page = queryParams.getOrDefault("page", "page1");

                
                // 获取Content-Type
                String contentType = exchange.getRequestHeaders().getFirst("Content-Type");
                if (contentType == null || !contentType.startsWith("multipart/form-data")) {
                    sendErrorResponse(exchange, 400, "请求必须是multipart/form-data类型");
                    return;
                }
                

                
                // 解析boundary
                String boundary = null;
                if (contentType.contains("boundary=")) {
                    boundary = contentType.substring(contentType.indexOf("boundary=") + 9);
                    // 如果boundary被引号包围，去除引号
                    if (boundary.startsWith("\"") && boundary.endsWith("\"")) {
                        boundary = boundary.substring(1, boundary.length() - 1);
                    }
                }

                
                if (boundary == null) {
                    sendErrorResponse(exchange, 400, "无法确定表单边界");
                    return;
                }
                
                // 读取请求体
                ByteArrayOutputStream requestData = new ByteArrayOutputStream();
                try (InputStream is = exchange.getRequestBody()) {
                    byte[] buffer = new byte[4096];
                    int bytesRead;
                    while ((bytesRead = is.read(buffer)) != -1) {
                        requestData.write(buffer, 0, bytesRead);
                    }
                }
                

                
                // 解析multipart/form-data格式
                byte[] data = requestData.toByteArray();
                Map<String, Object> formData = parseMultipartFormData(data, boundary);
                
                if (formData == null || !formData.containsKey("file") || formData.get("file") == null) {
                    sendErrorResponse(exchange, 400, "未能解析上传的文件");
                    return;
                }
                
                // 获取文件数据
                MultipartFile fileData = (MultipartFile) formData.get("file");
                String fileName = fileData.getFileName();
                byte[] fileContent = fileData.getContent();
                

                
                // 检查文件扩展名
                if (fileName == null || (!fileName.toLowerCase().endsWith(".xlsx") && !fileName.toLowerCase().endsWith(".xls"))) {
                    sendErrorResponse(exchange, 400, "请上传有效的Excel文件(.xlsx或.xls格式)");
                    return;
                }
                
                if (fileContent == null || fileContent.length == 0) {
                    sendErrorResponse(exchange, 400, "文件内容为空");
                    return;
                }
                

                
                // 使用POI解析Excel数据
                List<Map<String, Object>> excelData;
                
                try {
                    excelData = parseExcelFile(fileContent, fileName, page);
                    if (excelData.isEmpty()) {
                        sendErrorResponse(exchange, 400, "Excel文件不包含数据或格式不正确");
                        return;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    sendErrorResponse(exchange, 400, "无法解析Excel文件: " + e.getMessage());
                    return;
                }
                

                
                // 导入Excel数据，获取导入结果（成功和重复数量）
                Map<String, Integer> importResult = DatabaseHelper.importFromExcel(page, excelData, null);
                int successCount = importResult.getOrDefault("success", 0);
                int duplicateCount = importResult.getOrDefault("duplicate", 0);
                

                
                Map<String, Object> importData = new HashMap<>();
                importData.put("success", successCount);
                importData.put("duplicate", duplicateCount);
                importData.put("total", excelData.size());
                
                Map<String, Object> response = new HashMap<>();
                response.put("success", true);
                response.put("message", "导入Excel数据完成");
                response.put("data", importData);
                

                sendJsonResponse(exchange, response);
            } catch (Exception e) {

                e.printStackTrace();
                sendErrorResponse(exchange, 500, "导入Excel失败: " + e.getMessage());
            }
        }
        
        // 定义一个内部类来表示multipart表单中的文件
        static class MultipartFile {
            private String fileName;
            private String contentType;
            private byte[] content;
            
            public MultipartFile(String fileName, String contentType, byte[] content) {
                this.fileName = fileName;
                this.contentType = contentType;
                this.content = content;
            }
            
            public String getFileName() {
                return fileName;
            }
            
            public String getContentType() {
                return contentType;
            }
            
            public byte[] getContent() {
                return content;
            }
        }
        
        // 解析multipart/form-data格式的请求体
        private Map<String, Object> parseMultipartFormData(byte[] data, String boundary) {
            try {
                Map<String, Object> result = new HashMap<>();
                String boundaryLine = "--" + boundary;
                String endBoundary = boundaryLine + "--";
                
                // 将字节数组转换为字符串，用于查找边界
                String dataStr = new String(data, "ISO-8859-1");
                
                // 查找所有边界的位置
                List<Integer> boundaryPositions = new ArrayList<>();
                int pos = 0;
                while ((pos = dataStr.indexOf(boundaryLine, pos)) != -1) {
                    boundaryPositions.add(pos);
                    pos += boundaryLine.length();
                }
                
                // 解析每个部分
                for (int i = 0; i < boundaryPositions.size() - 1; i++) {
                    int start = boundaryPositions.get(i);
                    int end = boundaryPositions.get(i + 1);
                    
                    // 提取该部分的数据
                    String part = dataStr.substring(start + boundaryLine.length(), end);
                    
                    // 查找头部和主体的分隔位置（两个连续的换行符）
                    int headersEnd = part.indexOf("\r\n\r\n");
                    if (headersEnd == -1) continue;
                    
                    // 提取头部和内容
                    String headers = part.substring(0, headersEnd);
                    byte[] content = Arrays.copyOfRange(
                        data, 
                        start + boundaryLine.length() + headersEnd + 4, 
                        end - 2  // 减去结尾的\r\n
                    );
                    
                    // 解析Content-Disposition头
                    String name = null;
                    String fileName = null;
                    String contentType = null;
                    
                    // 查找Content-Disposition头
                    int dispositionStart = headers.indexOf("Content-Disposition:");
                    if (dispositionStart != -1) {
                        int dispositionEnd = headers.indexOf("\r\n", dispositionStart);
                        if (dispositionEnd == -1) dispositionEnd = headers.length();
                        String disposition = headers.substring(dispositionStart, dispositionEnd);
                        
                        // 提取name属性
                        int nameStart = disposition.indexOf("name=\"");
                        if (nameStart != -1) {
                            int nameEnd = disposition.indexOf("\"", nameStart + 6);
                            if (nameEnd != -1) {
                                name = disposition.substring(nameStart + 6, nameEnd);
                            }
                        }
                        
                        // 提取filename属性
                        int fileNameStart = disposition.indexOf("filename=\"");
                        if (fileNameStart != -1) {
                            int fileNameEnd = disposition.indexOf("\"", fileNameStart + 10);
                            if (fileNameEnd != -1) {
                                fileName = disposition.substring(fileNameStart + 10, fileNameEnd);
                            }
                        }
                    }
                    
                    // 查找Content-Type头
                    int contentTypeStart = headers.indexOf("Content-Type:");
                    if (contentTypeStart != -1) {
                        int contentTypeEnd = headers.indexOf("\r\n", contentTypeStart);
                        if (contentTypeEnd == -1) contentTypeEnd = headers.length();
                        String contentTypeHeader = headers.substring(contentTypeStart, contentTypeEnd);
                        
                        // 提取Content-Type值
                        int typeValueStart = contentTypeHeader.indexOf(":") + 1;
                        if (typeValueStart != 0) {
                            contentType = contentTypeHeader.substring(typeValueStart).trim();
                        }
                    }
                    
                    // 存储解析结果
                    if (name != null) {
                        if (fileName != null) {
                            // 这是一个文件
                            result.put(name, new MultipartFile(fileName, contentType, content));
                        } else {
                            // 这是一个普通表单字段
                            result.put(name, new String(content, "UTF-8"));
                        }
                    }
                }
                
                return result;
            } catch (Exception e) {

                e.printStackTrace();
                return null;
            }
        }
        
        // 使用POI解析Excel文件
        private List<Map<String, Object>> parseExcelFile(byte[] fileContent, String fileName, String page) {
            List<Map<String, Object>> result = new ArrayList<>();
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            
            try (InputStream is = new ByteArrayInputStream(fileContent)) {
                Workbook workbook;
                
                // 根据文件扩展名创建相应的Workbook
                if (fileName.toLowerCase().endsWith(".xlsx")) {
                    workbook = new XSSFWorkbook(is);
                } else {
                    workbook = new HSSFWorkbook(is);
                }
                
                // 获取第一个工作表
                Sheet sheet = workbook.getSheetAt(0);
                if (sheet == null) {
        
                    return result;
                }
                
                // 获取表头行
                Row headerRow = sheet.getRow(0);
                if (headerRow == null) {
        
                    return result;
                }
                
                // 解析表头
                Map<Integer, String> headerMap = new HashMap<>();
                for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                    Cell cell = headerRow.getCell(i);
                    if (cell != null) {
                        headerMap.put(i, cell.getStringCellValue().trim());
                    }
                }
                
    
                
                // 定义页面需要的字段
                Map<String, String> page1FieldMapping = new HashMap<>();
                page1FieldMapping.put("供应商", "supplier");
                page1FieldMapping.put("物料编码", "material_code");
                page1FieldMapping.put("物料名称", "material_name");
                page1FieldMapping.put("规格型号", "specification");
                page1FieldMapping.put("单位", "unit");
                page1FieldMapping.put("数量", "quantity");
                page1FieldMapping.put("批次", "batch_number");
                page1FieldMapping.put("到货日期", "arrival_date");
                page1FieldMapping.put("克隆号", "clone_number");
                page1FieldMapping.put("到期日期", "expiry_date");
                
                Map<String, String> page2FieldMapping = new HashMap<>();
                page2FieldMapping.put("时间", "use_time");
                page2FieldMapping.put("实验号", "material_code");
                page2FieldMapping.put("分组", "group_names");
                page2FieldMapping.put("详细项目", "details");
                
                // 选择正确的字段映射
                Map<String, String> fieldMapping = "page1".equals(page) ? page1FieldMapping : page2FieldMapping;
                
                // 验证必需的列存在
                boolean hasRequiredColumns = true;
                for (Map.Entry<String, String> entry : fieldMapping.entrySet()) {
                    boolean found = false;
                    for (String headerTitle : headerMap.values()) {
                        if (headerTitle.contains(entry.getKey())) {
                            found = true;
                            break;
                        }
                    }
                    
                    if (!found) {
    
                        hasRequiredColumns = false;
                    }
                }
                
                if (!hasRequiredColumns) {
        
                    return result;
                }
                
                // 解析数据行
                for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row == null) continue;
                    
                    // 检查是否为空行
                    boolean isEmptyRow = true;
                    for (int cellNum = 0; cellNum < headerMap.size(); cellNum++) {
                        Cell cell = row.getCell(cellNum);
                        if (cell != null && cell.getCellType() != CellType.BLANK) {
                            isEmptyRow = false;
                            break;
                        }
                    }
                    
                    if (isEmptyRow) continue;
                    
                    Map<String, Object> rowData = new HashMap<>();
                    
                    // 遍历每一列
                    for (int cellNum = 0; cellNum < headerMap.size(); cellNum++) {
                        String headerTitle = headerMap.get(cellNum);
                        if (headerTitle == null) continue;
                        
                        // 找到对应的数据库字段
                        String dbFieldName = null;
                        for (Map.Entry<String, String> entry : fieldMapping.entrySet()) {
                            if (headerTitle.contains(entry.getKey())) {
                                dbFieldName = entry.getValue();
                                break;
                            }
                        }
                        
                        if (dbFieldName == null) continue; // 跳过不需要的列
                        
                        Cell cell = row.getCell(cellNum);
                        if (cell == null) {
                            rowData.put(dbFieldName, null);
                            continue;
                        }
                        
                        // 根据单元格类型获取值
                        switch (cell.getCellType()) {
                            case STRING:
                                rowData.put(dbFieldName, cell.getStringCellValue());
                                break;
                            case NUMERIC:
                                // 如果是日期类型
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    rowData.put(dbFieldName, dateFormat.format(cell.getDateCellValue()));
                                } else {
                                    // 如果是整数
                                    if (cell.getNumericCellValue() == Math.floor(cell.getNumericCellValue())) {
                                        rowData.put(dbFieldName, (int) cell.getNumericCellValue());
                                    } else {
                                        rowData.put(dbFieldName, cell.getNumericCellValue());
                                    }
                                }
                                break;
                            case BOOLEAN:
                                rowData.put(dbFieldName, cell.getBooleanCellValue());
                                break;
                            case FORMULA:
                                try {
                                    rowData.put(dbFieldName, cell.getStringCellValue());
                                } catch (Exception e) {
                                    try {
                                        rowData.put(dbFieldName, cell.getNumericCellValue());
                                    } catch (Exception ex) {
                                        rowData.put(dbFieldName, "");
                                    }
                                }
                                break;
                            case BLANK:
                                rowData.put(dbFieldName, "");
                                break;
                            default:
                                rowData.put(dbFieldName, "");
                        }
                    }
                    
                    // 添加到结果列表
                    result.add(rowData);
                }
                
                workbook.close();
    
            } catch (Exception e) {
    
                e.printStackTrace();
            }
            
            return result;
        }
    }
    
    // 导入数据API处理器
    static class ImportApiHandler implements HttpHandler {
        @Override
        public void handle(HttpExchange exchange) throws IOException {
            if (!"POST".equals(exchange.getRequestMethod())) {
                sendErrorResponse(exchange, 405, "不支持的HTTP方法: " + exchange.getRequestMethod());
                return;
            }
            
            String query = exchange.getRequestURI().getQuery();
            Map<String, String> queryParams = parseQueryParams(query);
            
            try {
                // 确定操作的页面
                String page = queryParams.getOrDefault("page", "page1");
                
                // 读取请求体
                String requestBody = readRequestBody(exchange);
                List<Map<String, Object>> dataList = parseJsonToList(requestBody);
                
                // 批量导入数据，获取导入结果
                Map<String, Integer> importResult = DatabaseHelper.batchImport(page, dataList, null);
                int successCount = importResult.getOrDefault("success", 0);
                int duplicateCount = importResult.getOrDefault("duplicate", 0);
                
                Map<String, Object> response = new HashMap<>();
                response.put("code", 200);
                response.put("msg", "导入完成");
                response.put("success", successCount);
                response.put("duplicate", duplicateCount);
                response.put("total", dataList.size());
                
                sendJsonResponse(exchange, response);
            } catch (Exception e) {
                e.printStackTrace();
                sendErrorResponse(exchange, 500, "导入失败: " + e.getMessage());
            }
        }
    }

    // 工具方法：解析请求参数
    private static Map<String, String> parseQueryParams(String query) {
        Map<String, String> params = new HashMap<>();
        if (query == null || query.isEmpty()) {
            return params;
        }
        
        String[] pairs = query.split("&");
        for (String pair : pairs) {
            int idx = pair.indexOf("=");
            if (idx > 0) {
                String key = pair.substring(0, idx);
                String value = idx < pair.length() - 1 ? pair.substring(idx + 1) : "";
                params.put(key, value);
            }
        }
        
        return params;
    }
    
    // 工具方法：读取请求体
    private static String readRequestBody(HttpExchange exchange) throws IOException {
        try (InputStream is = exchange.getRequestBody();
             ByteArrayOutputStream os = new ByteArrayOutputStream()) {
            byte[] buffer = new byte[1024];
            int len;
            while ((len = is.read(buffer)) != -1) {
                os.write(buffer, 0, len);
            }
            return os.toString(StandardCharsets.UTF_8.name());
        }
    }
    
    // 工具方法：解析JSON字符串为Map
    @SuppressWarnings("unchecked")
    private static Map<String, Object> parseJsonToMap(String json) throws IOException {
        // 简单实现，实际可以使用JSON库如Jackson或Gson
        if (json == null || json.isEmpty()) {
            return new HashMap<>();
        }
        
        // 移除首尾的大括号
        json = json.trim();
        if (json.startsWith("{")) {
            json = json.substring(1);
        }
        if (json.endsWith("}")) {
            json = json.substring(0, json.length() - 1);
        }
        
        Map<String, Object> map = new HashMap<>();
        boolean inQuotes = false;
        boolean escaped = false;
        StringBuilder key = new StringBuilder();
        StringBuilder value = new StringBuilder();
        boolean isValue = false;
        
        for (int i = 0; i < json.length(); i++) {
            char c = json.charAt(i);
            
            if (escaped) {
                if (isValue) {
                    value.append(c);
                } else {
                    key.append(c);
                }
                escaped = false;
                continue;
            }
            
            if (c == '\\') {
                escaped = true;
                continue;
            }
            
            if (c == '"' && !inQuotes) {
                inQuotes = true;
                continue;
            }
            
            if (c == '"' && inQuotes) {
                inQuotes = false;
                continue;
            }
            
            if (c == ':' && !inQuotes && !isValue) {
                isValue = true;
                continue;
            }
            
            if ((c == ',' || i == json.length() - 1) && !inQuotes && isValue) {
                if (c != ',') {
                    value.append(c);
                }
                
                String keyStr = key.toString().trim();
                String valueStr = value.toString().trim();
                

                
                // 解析值的类型
                if (valueStr.startsWith("\"") && valueStr.endsWith("\"")) {
                    // 字符串
                    valueStr = valueStr.substring(1, valueStr.length() - 1);
                    map.put(keyStr, valueStr);

                } else if ("true".equals(valueStr) || "false".equals(valueStr)) {
                    // 布尔值
                    map.put(keyStr, Boolean.parseBoolean(valueStr));

                } else if (valueStr.matches("-?\\d+")) {
                    // 整数
                    map.put(keyStr, Integer.parseInt(valueStr));

                } else if (valueStr.matches("-?\\d+\\.\\d+")) {
                    // 浮点数
                    map.put(keyStr, Double.parseDouble(valueStr));

                } else if ("null".equals(valueStr)) {
                    // null值
                    map.put(keyStr, null);

                } else {
                    // 默认作为字符串
                    map.put(keyStr, valueStr);

                }
                
                key = new StringBuilder();
                value = new StringBuilder();
                isValue = false;
                continue;
            }
            
            if (isValue) {
                value.append(c);
            } else {
                key.append(c);
            }
        }
        
        // 处理最后一个字段（如果循环结束时还有未处理的键值对）
        if (isValue && key.length() > 0) {
            String keyStr = key.toString().trim();
            String valueStr = value.toString().trim();
            

            
            // 解析值的类型
            if (valueStr.startsWith("\"") && valueStr.endsWith("\"")) {
                // 字符串
                valueStr = valueStr.substring(1, valueStr.length() - 1);
                map.put(keyStr, valueStr);

            } else if ("true".equals(valueStr) || "false".equals(valueStr)) {
                // 布尔值
                map.put(keyStr, Boolean.parseBoolean(valueStr));

            } else if (valueStr.matches("-?\\d+")) {
                // 整数
                map.put(keyStr, Integer.parseInt(valueStr));

            } else if (valueStr.matches("-?\\d+\\.\\d+")) {
                // 浮点数
                map.put(keyStr, Double.parseDouble(valueStr));

            } else if ("null".equals(valueStr)) {
                // null值
                map.put(keyStr, null);

            } else {
                // 默认作为字符串
                map.put(keyStr, valueStr);

            }
        }
        
        return map;
    }
    
    // 工具方法：解析JSON字符串为List<Map<String, Object>>
    @SuppressWarnings("unchecked")
    private static List<Map<String, Object>> parseJsonToList(String json) throws IOException {
        // 简单实现，实际可以使用JSON库如Jackson或Gson
        List<Map<String, Object>> list = new ArrayList<>();
        if (json == null || json.isEmpty()) {
            return list;
        }
        
        // 移除首尾的中括号
        json = json.trim();
        if (json.startsWith("[")) {
            json = json.substring(1);
        }
        if (json.endsWith("]")) {
            json = json.substring(0, json.length() - 1);
        }
        
        // 分割为多个对象
        List<String> objectStrings = new ArrayList<>();
        int depth = 0;
        StringBuilder currentObject = new StringBuilder();
        boolean inQuotes = false;
        boolean escaped = false;
        
        for (int i = 0; i < json.length(); i++) {
            char c = json.charAt(i);
            
            if (escaped) {
                currentObject.append(c);
                escaped = false;
                continue;
            }
            
            if (c == '\\') {
                escaped = true;
                currentObject.append(c);
                continue;
            }
            
            if (c == '"') {
                inQuotes = !inQuotes;
            }
            
            if (c == '{' && !inQuotes) {
                depth++;
            }
            
            if (c == '}' && !inQuotes) {
                depth--;
            }
            
            currentObject.append(c);
            
            if (depth == 0 && !inQuotes && (c == '}' || c == ',')) {
                if (c == '}') {
                    objectStrings.add(currentObject.toString().trim());
                    currentObject = new StringBuilder();
                } else if (c == ',' && currentObject.toString().trim().endsWith("}")) {
                    objectStrings.add(currentObject.toString().trim().substring(0, currentObject.length() - 1));
                    currentObject = new StringBuilder();
                }
            }
        }
        
        // 解析每个对象
        for (String objectString : objectStrings) {
            if (!objectString.isEmpty()) {
                list.add(parseJsonToMap(objectString));
            }
        }
        
        return list;
    }
    
    // 工具方法：发送JSON响应
    private static void sendJsonResponse(HttpExchange exchange, Map<String, Object> response) throws IOException {
        String jsonResponse = mapToJson(response);
        byte[] responseBytes = jsonResponse.getBytes(StandardCharsets.UTF_8);
        
        exchange.getResponseHeaders().add("Content-Type", "application/json; charset=UTF-8");
        exchange.sendResponseHeaders(200, responseBytes.length);
        
        try (OutputStream os = exchange.getResponseBody()) {
            os.write(responseBytes);
        }
    }
    
    // 工具方法：发送错误响应
    private static void sendErrorResponse(HttpExchange exchange, int statusCode, String message) throws IOException {
        Map<String, Object> response = new HashMap<>();
        response.put("success", false);
        response.put("message", message);
        response.put("code", statusCode);
        
        String jsonResponse = mapToJson(response);
        byte[] responseBytes = jsonResponse.getBytes(StandardCharsets.UTF_8);
        
        exchange.getResponseHeaders().add("Content-Type", "application/json; charset=UTF-8");
        exchange.sendResponseHeaders(statusCode, responseBytes.length);
        
        try (OutputStream os = exchange.getResponseBody()) {
            os.write(responseBytes);
        }
    }
    
    // 工具方法：将Map转换为JSON字符串
    private static String mapToJson(Map<String, Object> map) {
        StringBuilder json = new StringBuilder();
        json.append("{");
        
        boolean first = true;
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            if (!first) {
                json.append(",");
            }
            first = false;
            
            json.append("\"").append(entry.getKey()).append("\":");
            
            Object value = entry.getValue();
            if (value == null) {
                json.append("null");
            } else if (value instanceof String) {
                json.append("\"").append(escapeJsonString((String) value)).append("\"");
            } else if (value instanceof Number) {
                json.append(value);
            } else if (value instanceof Boolean) {
                json.append(value);
            } else if (value instanceof Collection) {
                json.append(collectionToJson((Collection<?>) value));
            } else if (value instanceof Map) {
                json.append(mapToJson((Map<String, Object>) value));
            } else {
                json.append("\"").append(escapeJsonString(value.toString())).append("\"");
            }
        }
        
        json.append("}");
        return json.toString();
    }
    
    // 工具方法：将Collection转换为JSON字符串
    private static String collectionToJson(Collection<?> collection) {
        StringBuilder json = new StringBuilder();
        json.append("[");
        
        boolean first = true;
        for (Object item : collection) {
            if (!first) {
                json.append(",");
            }
            first = false;
            
            if (item == null) {
                json.append("null");
            } else if (item instanceof String) {
                json.append("\"").append(escapeJsonString((String) item)).append("\"");
            } else if (item instanceof Number) {
                json.append(item);
            } else if (item instanceof Boolean) {
                json.append(item);
            } else if (item instanceof Collection) {
                json.append(collectionToJson((Collection<?>) item));
            } else if (item instanceof Map) {
                json.append(mapToJson((Map<String, Object>) item));
            } else {
                json.append("\"").append(escapeJsonString(item.toString())).append("\"");
            }
        }
        
        json.append("]");
        return json.toString();
    }
    
    // 工具方法：转义JSON字符串中的特殊字符
    private static String escapeJsonString(String input) {
        if (input == null) {
            return "";
        }
        
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < input.length(); i++) {
            char c = input.charAt(i);
            switch (c) {
                case '"':
                    sb.append("\\\"");
                    break;
                case '\\':
                    sb.append("\\\\");
                    break;
                case '/':
                    sb.append("\\/");
                    break;
                case '\b':
                    sb.append("\\b");
                    break;
                case '\f':
                    sb.append("\\f");
                    break;
                case '\n':
                    sb.append("\\n");
                    break;
                case '\r':
                    sb.append("\\r");
                    break;
                case '\t':
                    sb.append("\\t");
                    break;
                default:
                    sb.append(c);
            }
        }
        return sb.toString();
    }
} 